package org.jugile.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.jugile.util.U;

public class DBConnection extends Jugile implements Iterable<List<Object>> {
	static Logger log = Logger.getLogger(DBConnection.class);
	private DBPool pool = null;
	private Connection conn = null;
	private ResultSet rs = null;
	
	public DBConnection(DBPool pool, Connection conn) throws SQLException {
		if (pool == null) U.fail("null pool");
		if (conn == null) U.fail("null connection");
		this.conn = conn;
		this.pool = pool;
		// ensure autocommit false
		if (conn.getAutoCommit()) {
			log.info("Note: Autocommit mode was true - setting to false");
			conn.setAutoCommit(false);
		}
	}

	public static DBConnection get() {
		DBPool p = DBPool.getPool();
		return p.getConnection();
	}
	
	public void free() {
		log.debug("dbconn free");
		try { commit(); } catch (Exception e) { fail(e); }
		pool.free(this);
	}
	
	public void commit() {
		log.debug("dbconn commit");
		try {
			if (rs != null) rs.close();
			conn.commit();
		} catch (Exception e) { fail(e); }
	}
	public void rollback() {
		log.debug("dbconn rollback");
		try {
			if (rs != null) rs.close();
			conn.rollback();
		} catch (Exception e) { fail(e); }
	}
	public void writeTx() throws SQLException {
		conn.setTransactionIsolation(conn.TRANSACTION_SERIALIZABLE);
	}
	public boolean isClosed() throws SQLException {
		return conn.isClosed();
	}
	protected void close() throws SQLException {
		log.debug("dbconn close");
		if (rs != null) rs.close();
		conn.close();
	}
	
	private Map<String, PreparedStatement> stmts = new HashMap<String,PreparedStatement>();
	public synchronized PreparedStatement prepareStmt(String sql, boolean update) throws SQLException {
		log.debug("prepareStatement: " + sql);
		PreparedStatement ps = stmts.get(sql);
		if (ps != null) {
			//U.log("  found.");
	        try {
	            ps.clearParameters(); // checks that it's ok
	            return ps;
	        } catch (Exception e) {
	            //U.log(e, "Reprepare statement : " + sql);
	        }
		}
		
		if (update)
			ps = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
		else 
			ps = conn.prepareStatement(sql);
		stmts.put(sql, ps);
		//U.log("  prepared: " + sql);
		return ps;
	}

	public int update(String sql) throws SQLException {
		PreparedStatement ps = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
		int res = ps.executeUpdate();
		ps.close();
		conn.commit();
		return res;
	}
	public int updateN(String sql) throws SQLException {
		PreparedStatement ps = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
		int res = ps.executeUpdate();
		ps.close();
		return res;
	}
	
	private PreparedStatement stmt;
	public PreparedStatement stmt() { return stmt; }
	private int pidx = 0;
	public void prepare(String sql) throws SQLException {
		boolean update = true;
		if (sql.toUpperCase().startsWith("SELECT ")) update = false; 
		stmt = prepareStmt(sql, update);
		pidx = 1;
	}
	public void param(Object p) throws SQLException {
		log.debug("  param: " + p);
		if (p instanceof String) stmt.setString(pidx, (String)p);
		else if (p instanceof Integer) stmt.setInt(pidx, (Integer)p);
		else if (p instanceof Time) stmt.setTimestamp(pidx, new java.sql.Timestamp(((Time)p).getMs()));
		else if (p instanceof IDO) stmt.setLong(pidx, ((IDO)p).id());
		else if (p instanceof EnumType) stmt.setInt(pidx, p == null?0:((EnumType)p).getValue());
		else if (p instanceof Money) stmt.setLong(pidx, p == null?0L:((Money)p).getCents());
		else stmt.setObject(pidx, p);
		pidx++;
	}
	public int execute() throws SQLException {
		pidx = 1;
		return stmt.executeUpdate();
	}
	public ResultSet executeQuery() throws SQLException {
		return stmt.executeQuery();
	}
	
	public List<List> select() throws Exception {
		List<List> res = new ArrayList<List>();
		ResultSet rs = stmt.executeQuery();
		ResultSetMetaData rsMetaData = rs.getMetaData();
		int fldCount = rsMetaData.getColumnCount();
		while (rs.next()) {
			ArrayList<Object> row = new ArrayList<Object>();
			for (int i = 0; i < fldCount; i++) {
				row.add(getObject(rs,rsMetaData,i));
			}
			res.add(row);
		}
		rs.close();
		return res;
	}
	
	public Object getObject(ResultSet rs, ResultSetMetaData rsmd, int idx) throws Exception {
		return rs.getObject(idx+1); 
	}
	
	public Iterator<List<Object>> iterator() {
		try {
			rs = stmt.executeQuery();
			rs.setFetchSize(1000);
			ResultSetMetaData rsMetaData = rs.getMetaData();
			int fldCount = rsMetaData.getColumnCount();
			RowIterator ri = new RowIterator(fldCount,rsMetaData);
			return ri;
		} catch (Exception e) { fail(e); }
		return null;
	}
	
	protected class RowIterator<E> implements Iterator {
		private int fldCount = 0;
		private boolean isEnd = true;
		private ResultSetMetaData rsmd;
		protected RowIterator(int fldCount, ResultSetMetaData rsmd) {
			this.fldCount = fldCount;
			this.rsmd = rsmd;
			getNext();
		}
		
		private ArrayList<Object> next = null;
		private void getNext() {
			try {
				isEnd = !rs.next();
				if (isEnd) {
					next = null;
					return;
				}
				next = new ArrayList<Object>();
				for (int i = 0; i < fldCount; i++) {
					next.add(getObject(rs,rsmd,i));
				}
			} catch (Exception e) { fail(e); }
		}
		
		public boolean hasNext() {
			return !isEnd;
		}

		public Object next() {
			Object res = next.clone();
			getNext();
			return res;
		}

		public void remove() {
			fail("remove not supported");			
		}
		
	}
	
	
	public List<List> select(String sql) throws Exception {
		List<List> res = new ArrayList<List>();
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		ResultSetMetaData rsMetaData = rs.getMetaData();
		int fldCount = rsMetaData.getColumnCount();
		while (rs.next()) {
			ArrayList<Object> row = new ArrayList<Object>();
			for (int i = 0; i < fldCount; i++) {
				row.add(getObject(rs,rsMetaData,i));
			}
			res.add(row);
		}
		rs.close();
		return res;
	}
	

}
